#!/usr/bin/env python
# -*- coding: UTF-8 -*-
'''
Created on 2011/03/24

@author: Rafael
'''
'''
This class creates the database and 
inserts the data retreived from the website
'''
import sqlite3
from GetInformation import GetInformation

from WriteInfo import WriteInfo
import os.path

class SqlInstructions():
    
    
    def createDB(self, ano):
        '''
        criacao das tabelas da base de dados
        '''
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""CREATE TABLE IF NOT EXISTS graus 
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS cursos
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS categorias
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS regimes
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS estabelecimentos
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS tipos_estabelecimento
                      (designacao TEXT PRIMARY KEY)
                   """)
        cursor.execute("""CREATE TABLE IF NOT EXISTS docentes
                      (nome_completo TEXT PRIMARY KEY, 
                       codigo INTEGER UNIQUE)
                   """)
#        cursor.execute("""CREATE TABLE IF NOT EXISTS fichas_curso
#                      (id INTEGER PRIMARY KEY AUTOINCREMENT, 
#                       ano INTEGER,
#                       docente REFERENCES docentes (nome_completo),
#                       grau REFERENCES graus (designacao),
#                       curso REFERENCES cursos (designacao))
#                   """)
    
        cursor.execute("""CREATE TABLE IF NOT EXISTS fichas_docencia
                      (id INTEGER PRIMARY KEY AUTOINCREMENT, 
                       ano INTEGER,
                       docente REFERENCES docentes (nome_completo),
                       estabelecimento REFERENCES estabelecimentos (designacao),
                       categoria REFERENCES categorias (designacao),
                       regime REFERENCES regimes (designacao),
                       tipo_estabelecimento REFERENCES 
                       tipos_estabelecimento (designacao),
                       grau REFERENCES graus (designacao),
                       curso REFERENCES cursos (designacao))
                   """)
        #conn.commit()
        cursor.close()
    pass
    
    def preencher_tipos_estabelecimento(self, ano, d_tipos_estabelecimento):
        '''
        preenchimento da tabela tipos_estabelecimento
        '''
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        #print "ANO: 200{0}".format(ano)
        #d_tipos_estabelecimento = GetInformation().obter_codigos_tipo_estabelecimento(ano)

        for tipo, codR in d_tipos_estabelecimento.iteritems():
            try:
                cmd = """insert into tipos_estabelecimento (designacao) values ('{0}')""".format(tipo)
                cursor.execute(cmd)
                conn.commit()
            except:
                pass
            pass
        cursor.close()
        return d_tipos_estabelecimento
    pass  
    
    def preencher_estabelecimentos(self, conn, cursor, d_estabelecimentos):
        '''
        preenchimento da tabela estabelecimentos
        '''
        for estabelecimento, codP in d_estabelecimentos.iteritems():
            cmd = """insert into {0} (designacao) values ('{1}')""".\
                format('estabelecimentos', estabelecimento)
        
            try:
                cursor.execute(cmd)
            except:
                continue
            pass   
        conn.commit()
    pass

    def preencher_geral(self, lista, conn, cursor, tipo):
        d_informacao = {}
        d_informacao['graus'] = 2
        d_informacao['cursos'] = 3
        d_informacao['categorias'] = 4
        d_informacao['regimes'] = 5
        d_informacao['estabelecimentos'] = 8

        for x in lista:
            #print x
            cmd = """insert into {0} (designacao) values ('{1}')""".\
                format(tipo, x)

            try:
                cursor.execute(cmd)
            except:
                #print tipo, x
                pass
            pass
        conn.commit()
        pass

    def preencher_docentes(self, lista, conn, cursor):
        C_CODIGO = 0
        C_DOCENTE = 1

        # uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                cmd = """insert into {0} (codigo, nome_completo) values ({1}, '{2}')""".\
                format('docentes', x[C_CODIGO], x[C_DOCENTE])
                #print cmd
                try:
                    cursor.execute(cmd)
                except:
                    #print x[C_DOCENTE]
                    continue
                pass
            pass
            conn.commit()
            pass

    def organizar_conjuntos_unicos(self, lista_entrada, tipo):
        d_informacao = {}
        d_informacao['graus'] = 2
        d_informacao['cursos'] = 3
        d_informacao['categorias'] = 4
        d_informacao['regimes'] = 5
        d_informacao['estabelecimentos'] = 8

        conjunto_unico = set()
        lista = []
        for ld in lista_entrada:
            for x in ld:
                conjunto_unico.add(x[d_informacao[tipo]])
                pass
        pass
    
        for y in conjunto_unico:
            lista.append(y)
        pass

        return lista
                     
    def preencher_fichas_curso(self, lista, conn, cursor):
        C_CODIGO = 0
        C_DOCENTE = 1
        C_GRAU = 2
        C_CURSO = 3
        C_ANO = 6

        #uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                #selecciona a referencia do grau
                cmd = """SELECT designacao FROM graus WHERE designacao = '{0}'""".\
                format(x[C_GRAU])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    grau = r[0]
                except:
                    continue

                # selecciona a referencia do curso
                cmd = """SELECT designacao FROM cursos WHERE designacao = '{0}'""".\
                format(x[C_CURSO])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    curso = r[0]
                except:
                    continue

                # selecciona a referencia do docente
                cmd = """SELECT nome_completo FROM docentes where nome_completo = '{0}'""".\
                format(x[C_DOCENTE])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    docente = r[0]
                except:
                    continue

                #insere na base de dados
                cmd = """INSERT INTO fichas_curso 
                     (ano, docente, grau, curso)
                     VALUES 
                     ('{0}','{1}','{2}','{3}') """.\
                     format(x[C_ANO], 
                       docente.encode('utf-8'), 
                       grau.encode('utf-8'), 
                       curso.encode('utf-8'))
                try:
                    cursor.execute(cmd)
                except:
                    continue
            pass   
        pass
        conn.commit()
        pass

    def preencher_fichas_docencia(self, lista, ano):
        C_CODIGO = 0
        C_DOCENTE = 1
        C_GRAU = 2
        C_CURSO = 3
        C_CATEGORIA = 4
        C_REGIME = 5
        C_ANO = 6
        C_TIPO_ENSINO = 7
        C_ESTABELECIMENTO = 8

        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        # uma linha corresponde a uma entrada 
        for linha in lista:
            for x in linha:
                # selecciona a referencia do docente
                cmd = """SELECT nome_completo FROM docentes WHERE nome_completo = '{0}'""".\
                format(x[C_DOCENTE])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    docente = r[0].encode('utf-8')
                    conn.commit()
                    #print docente
                except:
                    continue
                
                # selecciona a referencia do estabelecimento
                cmd = """SELECT designacao FROM estabelecimentos 
                     WHERE designacao = '{0}'""".\
                format(x[C_ESTABELECIMENTO])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    estabelecimento = r[0].encode('utf-8')
                    conn.commit()
                    #print estabelecimento
                except:
                    continue

                # selecciona a referencia da categoria
                cmd = """SELECT designacao FROM categorias WHERE designacao = '{0}'""".\
                format(x[C_CATEGORIA])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    categoria = r[0].encode('utf-8')
                    conn.commit()
                    #print categoria
                except:
                    continue

                # selecciona a referencia do regime
                cmd = """SELECT designacao FROM regimes WHERE designacao = '{0}'""".\
                format(x[C_REGIME])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    regime = r[0].encode('utf-8')
                    conn.commit()
                    #print regime
                except:
                    continue

                # selecciona a referencia do tipo de estabelecimento
                cmd = """SELECT designacao FROM tipos_estabelecimento 
                     WHERE designacao = '{0}'""".\
                format(x[C_TIPO_ENSINO])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    tipo_estabelecimento = r[0].encode('utf-8')
                    conn.commit()
                    #print tipo_estabelecimento
                except:
                    continue
                
                #selecciona a referencia do grau
                cmd = """SELECT designacao FROM graus WHERE designacao = '{0}'""".\
                format(x[C_GRAU])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    grau = r[0].encode('utf-8')
                except:
                    continue

                # selecciona a referencia do curso
                cmd = """SELECT designacao FROM cursos WHERE designacao = '{0}'""".\
                format(x[C_CURSO])
                try:
                    cursor.execute(cmd)
                    r = cursor.fetchone()
                    curso = r[0].encode('utf-8')
                except:
                    continue
                
                # insere na base de dados
                cmd = """INSERT INTO fichas_docencia 
                     (ano, 
                      docente, 
                      estabelecimento, 
                      categoria,
                      regime, 
                      tipo_estabelecimento,
                      grau,
                      curso)
                     VALUES 
                     ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}') 
                  """.format(x[C_ANO], docente, estabelecimento, categoria, 
                             regime, tipo_estabelecimento, grau, curso)
                #print cmd  
                try:
                    cursor.execute(cmd)
                    conn.commit()
                except:
                    continue
                pass
            pass
        conn.commit()
        cursor.close()
        pass
    
    def preencher_tabelas_gerais(self, ano):
    
        
        getInfo = GetInformation()
        self.createDB(ano)

        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        #print "ANO: 200{0}".format(ano)
        d_tipos_estabelecimento = getInfo.obter_codigos_tipo_estabelecimento(ano)

        self.preencher_tipos_estabelecimento(ano, d_tipos_estabelecimento)
        
        lista = []
        for tipo, codR in d_tipos_estabelecimento.iteritems():
            print tipo, ' ', codR

            d_estabelecimentos = getInfo.obter_codigos_estabelecimentos(ano, codR)
        
            # coloca na base de dados 
            self.preencher_estabelecimentos(conn, cursor, d_estabelecimentos)

            for estabelecimento, codP in d_estabelecimentos.iteritems():
                # obtem a informacao de um docente
                ld = getInfo.obter_informacao_docentes(ano, tipo, 
                                           codR, estabelecimento, codP)
                
                lista.append(ld)
                pass
            pass

        #preenche a informacao geral comum
        lista_unica = self.organizar_conjuntos_unicos(lista, 'graus')
        self.preencher_geral(lista_unica, conn, cursor, 'graus')

        lista_unica = self.organizar_conjuntos_unicos(lista, 'cursos')
        self.preencher_geral(lista_unica, conn, cursor, 'cursos')

        lista_unica = self.organizar_conjuntos_unicos(lista, 'regimes')
        self.preencher_geral(lista_unica, conn, cursor, 'regimes')

        lista_unica = self.organizar_conjuntos_unicos(lista, 'categorias')
        self.preencher_geral(lista_unica, conn, cursor, 'categorias')

        #preenchimento da informacao de cada docente
        self.preencher_docentes(lista, conn, cursor)

        #preencher ficha de cursos
        #self.preencher_fichas_curso(lista, conn, cursor)
        #cursor.close()

        #preencher fichas docencia
        self.preencher_fichas_docencia(lista, ano)
        cursor.close()
        
        #self.doQueries(ano)

        pass
    
    #do the queries to create the html pages
    def doQueriesHTML(self, ano):
        
        #dados para metodo indexAno
        nDocentes = self.numberTeachers(ano)
        listTipo = self.listTipoInst(ano)
        nInst = self.nInst(ano)
        nGraus = self.nGraus(ano)
        
        #dados para metodo tiposHTML
        nDocenteTipo = self.numberTeachersPerTipo(ano)
        listInstPerTipo = self.listInstPerTipo(ano)
        nInstPerTipo = self.nInstPerTipo(ano)
        nDocenteGrausTipo = self.nDocenteGrausTipo(ano)
        nGrausTipo = self.nGrausTipo(ano)
        
        #dados para metodo listDocenteInst
        teachersPerInst = self.teachersPerInst(ano)
        listInst = self.listInst(ano)
        teacherPerDegreePerInst = self.teacherPerDegreePerInst(ano)
        nTeachersPerInst = self.nTeachersPerInst(ano)
        
        writeInfo = WriteInfo()
        writeInfo.createHtml(ano, nDocentes,listTipo, nInst, nGraus, nDocenteTipo, 
                             listInstPerTipo,nInstPerTipo, nDocenteGrausTipo, nGrausTipo,
                                teacherPerDegreePerInst,teachersPerInst,nTeachersPerInst, listInst)
        
        pass
    
    #create a query with the data choosen by the user
    def doQueriesCSV(self, ano, campos):

        i = len(campos)
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        group = ""
        cmd = "SELECT "
        for campo in campos:
            cmd += campo
            group += campo
            i = i - 1
            if(i != 0):
                cmd += ","
                group += ","
                pass
            pass
        cmd += " FROM fichas_docencia GROUP BY "
        cmd += group
        #print cmd
        cursor.execute(cmd)
        dados = cursor.fetchall()
        writeInfo = WriteInfo()
        writeInfo.escrita_csv(ano, dados)

        pass

    """dados para metodo WriteInfo.indexAno()"""
    #Retorna o numero de professores num ano
    def numberTeachers(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        
        cursor.execute("""SELECT COUNT(DISTINCT nome_completo) FROM docentes""")
        nDocente = cursor.fetchone()
        conn.close()
        return nDocente[0]
        pass
    
    #Retorna os tipos de instituicao
    def listTipoInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        
        cursor.execute("""SELECT designacao FROM tipos_estabelecimento GROUP BY designacao""")
        nDocente = cursor.fetchall()
        conn.close()
        return nDocente
        pass
    
    #Retorna a numero de instituicoes
    def nInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT COUNT(DISTINCT designacao) FROM estabelecimentos""")
        nDocente = cursor.fetchone()
        return nDocente
        pass
    
    #numero de docentes por grau num ano
    def nGraus(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT grau, COUNT(docente) FROM fichas_docencia GROUP BY grau""")
        nDocente = cursor.fetchall()
        return nDocente
        pass
    
    """dados para a função WriteInfo.tipoHtml()"""
    #Retorna o numero de professores por tipo num ano
    def numberTeachersPerTipo(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        
        cursor.execute("""SELECT tipo_estabelecimento, COUNT(DISTINCT docente) 
                        FROM fichas_docencia GROUP BY tipo_estabelecimento""")
        nDocenteTipo = cursor.fetchall()
        conn.close()
        return nDocenteTipo
        pass
    
    #Retorna a lista de instituições por tipo num ano
    def listInstPerTipo(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        #nDocente = []
        cursor.execute("""SELECT DISTINCT tipo_estabelecimento, estabelecimento 
                        FROM fichas_docencia GROUP BY tipo_estabelecimento,estabelecimento""")
        nDocente = cursor.fetchall()
        return nDocente
        pass
    
    #Retorna o numero de instituições por tipo num ano
    def nInstPerTipo(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT DISTINCT tipo_estabelecimento, count(distinct estabelecimento) 
                        FROM fichas_docencia GROUP BY tipo_estabelecimento""")
        nDocente = cursor.fetchall()
        return nDocente
        pass
    
    #numero de docentes por grau por tipo de estabelecimento por ano
    def nDocenteGrausTipo(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT DISTINCT tipo_estabelecimento,grau, COUNT(DISTINCT docente) 
                            FROM fichas_docencia GROUP BY tipo_estabelecimento, grau""")
        nDocente = cursor.fetchall()
        return nDocente
        pass
    
    #numero de grau por tipo de estabelecimento por ano
    def nGrausTipo(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT tipo_estabelecimento,COUNT(DISTINCT grau) 
                            FROM fichas_docencia GROUP BY tipo_estabelecimento""")
        nDocente = cursor.fetchall()
        return nDocente
        pass
    
    """dados para a função WriteInfo.listDocenteInst()"""
    #Retorna a lista de instituicao num ano
    def listInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT DISTINCT estabelecimento FROM fichas_docencia GROUP BY estabelecimento""")
        listInst = cursor.fetchall()
        conn.close()
        return listInst
        pass
    
    #Retorna a lista de professores numa instituicao num ano
    def teachersPerInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT DISTINCT estabelecimento, docente 
                        FROM fichas_docencia GROUP BY estabelecimento, docente""")
        teachersPerInst = cursor.fetchall()
        conn.close()
        return teachersPerInst
        pass
    
    #Retorna o numero de professores numa instituicao num ano
    def nTeachersPerInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        cursor.execute("""SELECT DISTINCT estabelecimento, COUNT(DISTINCT docente) 
                        FROM fichas_docencia GROUP BY estabelecimento""")
        nTeachersPerInst = cursor.fetchall()
        conn.close()
        return nTeachersPerInst
        pass
    
    #Cria um dicionario com o dos estabelecimentos, com outro dicionario
    #com os graus e numeros de docente
    def teacherPerDegreePerInst(self, ano):
        conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
        cursor = conn.cursor()
        
        nDocEstabGrau = {}
        
        cursor.execute("""SELECT estabelecimento, COUNT(DISTINCT grau) as TotalGraus FROM fichas_docencia 
                        GROUP BY estabelecimento""")
        estabelecimentos = cursor.fetchall()
        cursor.execute("""SELECT estabelecimento,grau, COUNT(docente) as Total FROM fichas_docencia 
                        GROUP BY estabelecimento,grau""")
        for row in estabelecimentos:
            #print row[0],"--",row[1]
            nDocentesGrau = {}
            for i in range(0,row[1]):
                grau = cursor.fetchone()
                nDocentesGrau[grau[1]] = grau[2]
                #print grau[1],"--",grau[2]
                pass
            nDocEstabGrau[row[0]] = nDocentesGrau
            pass
        return nDocEstabGrau
        pass

    """Dados para Criar os Graficos"""
    #do the queries to create a graphic with the options choosen by the user
    def doQueriesGraf(self,ano, tipoGraf):
        
        if(tipoGraf == "nDocentesAno"):
            nDocentes = self.numberTeachers(ano)
            return nDocentes
            pass
        
        if(tipoGraf == "nDocentesInst"):
            inst = []
            nDocente = []
            info = []
            try:
                conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
                cursor = conn.cursor()
                cmd = """SELECT     estabelecimento,
                     COUNT(DISTINCT docente) AS Total_docentes
                     FROM       fichas_docencia
                     GROUP BY   estabelecimento
                     ORDER by   Total_docentes DESC
                     LIMIT 10"""
                cursor.execute(cmd)
                r = cursor.fetchall()   
                for row in r:
                    inst.append(row[0])
                    nDocente.append(row[1])
                    pass           
            except:
                print ('')
                pass
            info.append(inst)
            info.append(nDocente)
            return info
        
        if(tipoGraf == "GrauDocentes"):
            conn = sqlite3.connect('db/rebides0{0}.db'.format(ano))
            cursor = conn.cursor()
            info = {}
            
            try:
                cmd = """SELECT     grau,
                     COUNT(DISTINCT docente) AS Total_docentes
                     FROM       fichas_docencia
                     GROUP BY   grau
                     ORDER by   Total_docentes DESC
                     LIMIT 4"""
                cursor.execute(cmd)
                r = cursor.fetchall()

                for row in r:
                    info[row[0]] = row[1]
                    pass
                pass
            except:
                print ('')
                pass
            return info
        pass

    pass